﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using Lswweb.IDAL;
using Lswweb.Model;
using Lswweb.Common;

namespace Lswweb.SqlServerDAL
{
    public class ProductsDAL : IProducts
    {
        private Builder<ProductInfo> builder = new Builder<ProductInfo>("Products", "Id");
        private string _orderBy = "DisplayOrder asc,Id desc";

        public ProductsDAL()
        {
            builder.GetParameters += new ParametersBuilder<ProductInfo>(GetParameters);
            builder.CreateModel += new ReaderBind<ProductInfo>(ReaderBind);
        }

        #region IProducts 成员

        /// <summary>
        /// 获取产品实体
        /// </summary>
        /// <param name="productId"></param>
        /// <returns></returns>
        public ProductInfo GetProduct(int productId)
        {
            ProductInfo productInfo=builder.GetModel(productId);
            if(productInfo!=null)
                productInfo.ImageFiles=GetImages(productId);

            return productInfo;
        }
        /// <summary>
        /// 获取同类产品下一条 记录
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public ProductInfo GetNextProduct(int productId, int categoryId)
        {
            string fields = "Id,Title,ProductCategoryId";
            string where = "Id<@Id and ProductCategoryId=@ProductCategoryId";
            IDataParameter[] parameters = new IDataParameter[]{
                DataAccess.CreateParameter("Id", productId),
                DataAccess.CreateParameter("ProductCategoryId", categoryId)};

            return builder.GetModel("Products", fields, where, "DisplayOrder asc,Id desc", parameters);
        }
        /// <summary>
        /// 获取同类产品上一条 记录
        /// </summary>
        /// <param name="productId"></param>
        /// <param name="categoryId"></param>
        /// <returns></returns>
        public ProductInfo GetPrevProduct(int productId, int categoryId)
        {
            string fields = "Id,Title,ProductCategoryId";
            string where = "Id>@Id and ProductCategoryId=@ProductCategoryId";
            IDataParameter[] parameters = new IDataParameter[]{
                DataAccess.CreateParameter("Id", productId),
                DataAccess.CreateParameter("ProductCategoryId", categoryId)};

            return builder.GetModel("Products", fields, where, "DisplayOrder asc,Id asc", parameters);
        }

        public bool UpdateDisplayOrder(int productId, int displayOrder)
        {
            if (productId <= 0 || displayOrder <= 0)
                return false;

            return builder.UpdateFields("DisplayOrder", productId, displayOrder);
        }

        public IList<ProductInfo> GetProducts(string searchKey, SearchKeyType searchKeyType, int categoryId, Boolean? isWholesale, StatusType? type, AuditingStatusType? auditingStatus, bool? isHot, bool? isTop, Pair<DateTime?> dateTimeRange, Pair<Decimal?> priceRange, string orderBy, string ver, int pageIndex, int pageSize, out int totalRecord)
        {
            string fileds = "Id,ProductCategoryId,IsHot,IsTop,DateCreated,Title,Promulgator,ImageUrl ,DisplayOrder ,AuditingStatus ,Status ,Price,MarketPrice,Sale,ProductCode,IsWholesale,Other,Summary";
            StringBuilder where = new StringBuilder();
            IList<IDataParameter> list = new List<IDataParameter>();

            //SearchKey类别
            if (!string.IsNullOrEmpty(searchKey))
            {
                list.Add(DataAccess.CreateParameter("@SearchKey", searchKey));
                switch (searchKeyType)
                {
                    case SearchKeyType.Key:
                        where.Append(" and Keys like '%' + @SearchKey + '%'");
                        break;
                    case SearchKeyType.Promulgator:
                        where.Append(" and Promulgator=@SearchKey");
                        break;
                    case SearchKeyType.Title:
                    default:
                        where.Append(" and Title like '%' + @SearchKey + '%'");
                        break;
                }
            }

            //CategoryId条件
            if (categoryId > 0)
            {
                list.Add(DataAccess.CreateParameter("@CategoryId", categoryId));
                where.Append(" and exists(select Id from ProductCategories where  [Path] + '/' like (select [Path] + '/' from ProductCategories AS ProductCategories_1 where (Id = @CategoryId)) + '%' AND (Products.ProductCategoryId = Id))");
            }

            //Status条件
            if (type!=null)
            {
                list.Add(DataAccess.CreateParameter("@Status", type));

                where.Append(" and Status=@Status");
            }

            //AuditingStatus条件
            if (auditingStatus!=null)
            {
                list.Add(DataAccess.CreateParameter("@AuditingStatus", auditingStatus));

                where.Append(" and AuditingStatus=@AuditingStatus");
            }

            //IsTop条件
            if (isTop != null)
            {
                list.Add(DataAccess.CreateParameter("@IsTop", isTop));
                where.Append(" and IsTop=@IsTop");
            }

            //IsHot条件
            if (isHot != null)
            {
                list.Add(DataAccess.CreateParameter("@IsHot", isHot));
                where.Append(" and IsHot=@IsHot");
            }

        
                //maxDateTime条件
                if (dateTimeRange.Right != null)
                {
                    list.Add(DataAccess.CreateParameter("@MaxDateTime", dateTimeRange.Right));
                    where.Append(" and DateCreated < @MaxDateTime");
                }

                //minDateTime条件
                if (dateTimeRange.Left != null)
                {
                    list.Add(DataAccess.CreateParameter("@MinDateTime", dateTimeRange.Left));
                    where.Append(" and DateCreated > @MinDateTime");
                }
           
                //大于最小价格
                if (priceRange.Left != null)
                {
                    list.Add(DataAccess.CreateParameter("@MinPrice", (decimal)priceRange.Left));
                    where.Append(" and Price > @MinPrice");
                }

                //小于最大价格
                if (priceRange.Right != null)
                {
                    list.Add(DataAccess.CreateParameter("@MaxPrice", (decimal)priceRange.Right));
                    where.Append(" and Price < @MaxPrice");
                }
            

            if (!string.IsNullOrEmpty(ver))
            {
                list.Add(DataAccess.CreateParameter("Ver", ver));
                where.Append(" and Ver=@Ver");
            }

            if (isWholesale != null)
            {
                list.Add(DataAccess.CreateParameter("@IsWholesale", isWholesale.Value));
                where.Append(" and IsWholesale=@IsWholesale");
            }

            if (string.IsNullOrEmpty(orderBy))
                orderBy = this._orderBy;

            return builder.GetModels(fileds, (where.Length > 0) ? where.ToString().Remove(0, 5) : string.Empty, orderBy, pageIndex, pageSize, out totalRecord, list.ToArray());
        }

        /// <summary>
        /// 添加产品
        /// </summary>
        /// <param name="proInfo"></param>
        /// <returns></returns>
        public int Insert(ProductInfo proInfo)
        {
            return builder.Insert(
                proInfo,
                "ProductCategoryId",
                "Title",
                "Keys",
                "Summary",
                "Promulgator",
                "ImageUrl",
                "[Content]",
                "Status",
                "AuditingStatus",
                "IsHot",
                "IsTop",
                "DateCreated",
                "Ver",
            
                "Content2",
                "Content3",
                "Content4",
                "Price",
                "MarketPrice",
                "ProductCode",
                "Sale",
                "Other",
                "Weight",
                "IsWholesale");
        }

        public bool Update(ProductInfo proInfo)
        {
            if (proInfo.Id <= 0)
                return false;

            return builder.Update(
                proInfo,
                "Id=@Id",
                "ProductCategoryId",
                "Title",
                "Keys",
                "Summary",
                "Content",

                "Content2",
                "Content3",
                "Content4",
                "Price",
                "MarketPrice",
                "ProductCode",
                "Sale",
                "Other",
                "Weight",
                "IsWholesale");
        }

        public bool UpdateIsTop(int productId, bool isTop)
        {
            return builder.UpdateFields("IsTop", productId, isTop);
        }

        public bool UpdateStatus(int productId, StatusType type)
        {
            return builder.UpdateFields("Status", productId, (int)type);
        }
        public bool UpdateStatus(int productId, AuditingStatusType auditingStatus, string remark)
        {
            return builder.UpdateFields("AuditingStatus,Remark", productId, (int)auditingStatus, remark);
        }

        public bool UpdateIsHot(int productId, bool isHot)
        {
            return builder.UpdateFields("IsHot", productId, isHot);
        }
        public bool UpdateCategory(int productId, int categoryId)
        {
            if (categoryId < 0)
                return false;
            if (categoryId == 0)
                return builder.UpdateFields("ProductCategoryId", productId, null);
            else
                return builder.UpdateFields("ProductCategoryId", productId, categoryId);
        }

        public bool UpdateImageUrl(int productId, string imageUrl)
        {
            if (productId <= 0)
                return false;

            return builder.UpdateFields("ImageUrl", productId, imageUrl);
        }

        public object GetFieldValue(int productId, string fieldName)
        {
            if (productId <= 0)
                return null;

            return builder.GetFieldValue(fieldName, productId);
        }

        public bool Delete(int productId)
        {
            return builder.Delete(productId);
        }

        #endregion

        #region UpFileImages

        private IList<UpFileInfo> GetImages(int productId)
        {
            UpFilesDAL upFileDAL = new UpFilesDAL();
            return upFileDAL.GetFiles(productId);
        }

        public bool AddImage(int productId, int upFileId)
        {
            if (productId <= 0 || upFileId <= 0)
                return false;

            string sql = "insert into ProImgShow(ProId,FileId)  values(@ProId,@FileId);";
            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql,
                DataAccess.CreateParameter("@ProId", productId),
                DataAccess.CreateParameter("@FileId",upFileId)));
        }

        #endregion

        #region Protected Methods

        protected IDataParameter[] GetParameters(ProductInfo proInfo)
        {
            return new IDataParameter[]{
                DataAccess.CreateParameter("@Id",proInfo.Id),
                DataAccess.CreateParameter("@Title", proInfo.Title),
                DataAccess.CreateParameter("@Keys", proInfo.Keys),
                DataAccess.CreateParameter("@Summary", proInfo.Summary),
                DataAccess.CreateParameter("@Content", proInfo.Content),
                DataAccess.CreateParameter("@IsHot", proInfo.IsHot),
                DataAccess.CreateParameter("@IsTop", proInfo.IsTop),
                DataAccess.CreateParameter("@DisplayOrder", proInfo.DisplayOrder),
                DataAccess.CreateParameter("@Status", (int)proInfo.Status),
                DataAccess.CreateParameter("@AuditingStatus", proInfo.AuditingStatus),
                DataAccess.CreateParameter("@Ver", proInfo.Ver),
                DataAccess.CreateParameter("@Promulgator",proInfo.Promulgator),
                DataAccess.CreateParameter("@DateCreated", proInfo.DateCreated),
                DataAccess.CreateParameter("@ProductCategoryId",proInfo.ProductCategoryId),
                DataAccess.CreateParameter("@ImageUrl", proInfo.ImageUrl),

                DataAccess.CreateParameter("@Price", proInfo.Price),
                DataAccess.CreateParameter("@MarketPrice",proInfo.MarketPrice),
                DataAccess.CreateParameter("@Sale",proInfo.Sale),
                DataAccess.CreateParameter("@ProductCode",proInfo.ProductCode),
                DataAccess.CreateParameter("@Content2",proInfo.Content2),
                DataAccess.CreateParameter("@Content3",proInfo.Content3),
                DataAccess.CreateParameter("@Content4",proInfo.Content4),
                DataAccess.CreateParameter("@Other",proInfo.Other),
                DataAccess.CreateParameter("@Weight",proInfo.Weight),
                DataAccess.CreateParameter("@IsWholesale",proInfo.IsWholesale)};
        }

        protected ProductInfo ReaderBind(Hashtable dataReader)
        {
            ProductInfo model = new ProductInfo();

            model.Id = dataReader.Get<int>("Id");
            model.ProductCategoryId = dataReader.Get<int>("ProductCategoryId");
            model.IsHot = dataReader.Get<bool>("IsHot");
            model.IsTop = dataReader.Get<bool>("IsTop");
            model.DateCreated = dataReader.Get<DateTime>("DateCreated");
            model.Title = dataReader.Get<string>("Title");
            model.Keys = dataReader.Get<string>("Keys");
            model.Summary = dataReader.Get<string>("Summary");
            model.ImageUrl = dataReader.Get<string>("ImageUrl");
            model.Content = dataReader.Get<string>("Content");
            model.Status = dataReader.Get<StatusType>("Status");
            model.AuditingStatus = dataReader.Get<AuditingStatusType>("AuditingStatus");
            model.Ver = dataReader.Get<string>("Ver");
            model.DisplayOrder = dataReader.Get<int>("DisplayOrder");

            model.Price = dataReader.Get<decimal>("Price");
            model.ProductCode = dataReader.Get<string>("ProductCode");
            model.MarketPrice = dataReader.Get<decimal>("MarketPrice");
            model.Sale = dataReader.Get<int>("Sale");
            model.Content2 = dataReader.Get<string>("Content2");
            model.Content3 = dataReader.Get<string>("Content3");
            model.Content4 = dataReader.Get<string>("Content4");
            model.Other = dataReader.Get<string>("Other");
            model.Weight = dataReader.Get<int>("Weight");
            model.IsWholesale = dataReader.Get<Boolean>("IsWholesale");

            return model;
        }

        #endregion
    }
}
